**call report
**total assets, total liabilities, and total deposits
global path "~/Dropbox (CBS)/balance sheet paper replication"
cd "$path/call_report"
fs *.txt
foreach f in `r(files)'{
	import delimited "`f'",clear
	foreach var of varlist *{
		label variable `var' "`=`var'[1]'"
	}
	drop in 1
	gen reporting_period=date(reportingperiodenddate,"YMD")
	format %td reporting_period
	sort idrssd reporting_period
	gen year=year(reporting_period)
	destring rcon2200 rcon2170 rcon2948 rcfd2170 rcfd2948,replace
	gen total_assets=rcon2170
	replace total_assets=rcfd2170 if missing(total_assets)
	gen total_liabilities=rcon2948
	replace total_liabilities=rcfd2948 if missing(total_liabilities)
	collapse (mean) year total_deposits=rcon2200 total_assets total_liabilities,by(idrssd)
	rename idrssd rssdid
	tostring rssdid,replace
	replace rssdid=substr("0000000000",1,10-length(rssdid))+rssdid
	save "`=year[1]'"
}

clear
fs *.dta
foreach f in `r(files)'{
	append using "`f'"
}
sort rssdid year
save balance_sheet.dta

**upbrd487 capital ratio
cd "$path/capital_ratio"
forvalues i=2007/2015{
import delimited using "FFIEC CDR UBPR Ratios Capital Analysis-a `i'.txt",delimiter(tab) clear
  
foreach var of varlist *{
label variable `var' "`=`var'[1]'"
}
drop in 1

sort idrssd reportingperiod
destring ubprd487,replace
collapse (mean) cr=ubprd487, by(idrssd)
rename idrssd rssdid
tostring rssdid,replace
replace rssdid=substr("0000000000",1,10-length(rssdid))+rssdid
gen year=`i'
save "cr`i'.dta"
}

clear
forvalues i=2007/2015{
	append using "cr`i'.dta"
}
sort rssdid year
save capital_ratio.dta

cd ..
use "./call_report/balance_sheet",clear
merge 1:1 rssdid year using "./capital_ratio/capital_ratio.dta"
drop _m
save balance_sheet.dta


**core/non core deposits as a fraction total assets
cd "$path/ubpr_data"
forvalues i=2007/2015{
	import delimited using "FFIEC CDR UBPR Ratios Liquidity and Funding `i'.txt",clear

	foreach var of varlist *{
	label variable `var' "`=`var'[1]'"
	}
	drop in 1

	sort idrssd reportingperiod
	destring ubpre591 ubpre592,replace
	collapse (mean) core_deposits=ubpre591 noncore_funding=ubpre592, by(idrssd)
	rename idrssd rssdid
	tostring rssdid,replace
	replace rssdid=substr("0000000000",1,10-length(rssdid))+rssdid
	gen year=`i'
	save "deposits`i'.dta"
}

clear
forvalues i=2007/2015{
	append using "deposits`i'.dta"
}
sort rssdid year
save deposits.dta
